package Server.DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Server.Message.MessageReport1Replay;
import Server.Message.MessageReport2Replay;
import Server.Message.MessageReport3Replay;
import Server.Message.MessageReportReplay;
/** 
 * 
 *class Reports
 */
public class Reports extends Query {

	
/**
 * Reports constructor
 */
	public Reports() {
		super();
	}
	
	public Report getReport(int id) throws SQLException{
		setQuery("SELECT * FROM report WHERE idreport='"+id+"'");
		ResultSet rs = execQuery();
		if (rs.next()) return new Report(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getString(4));
		 return null;
	}
	
	public ArrayList<ReportIF> getReportsIF() throws SQLException{
		ArrayList<ReportIF> array =new ArrayList<ReportIF>();
		setQuery("SELECT * FROM report");
		ResultSet rs = execQuery();
		while (rs.next()) array.add(new Report(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getString(4)));
		return array;
	}
	
	public MessageReportReplay getQuery(int report, int id) throws SQLException{
		ArrayList<Float> fa=new ArrayList<Float>() ;
		ArrayList<String> sa = new ArrayList<String>();
		Report r = getReport(report);
		setQuery(r.getStr());
		PreparedStatement q = (PreparedStatement) getPS();
		q.setInt(1, id);
		ResultSet rs = q.executeQuery();
		while(rs.next())
		{
			sa.add(rs.getString(1));
			fa.add(rs.getFloat(2));
		}
		return new MessageReportReplay(fa, sa);
	}
	
	
	
	/**
	 * 
	 * @param idteacher
	 * @return report of grade all throw classes of a teacher 
	 * @throws SQLException
	 */
	public MessageReport1Replay getTeachersClasses(int idteacher) throws SQLException{
		ArrayList<Float> grades=new ArrayList<Float>() ;
		ArrayList<String> classname = new ArrayList<String>();
		setQuery("SELECT class.className,avg(grade) as avg " +
				"FROM lesson,class,reg " +
				"WHERE lesson.idlesson=reg.idlesson " +
				"AND lesson.idclass=class.idclass " +
				"AND reg.grade!='0' "+
				"AND lesson.idteacher='"+idteacher+"' " +
						"GROUP BY class.idclass");

		ResultSet rs = execQuery();
		while(rs.next())
		{
			classname.add(rs.getString(1));
			grades.add(rs.getFloat(2));
		}
		return new MessageReport1Replay(grades, classname);
	}
	/**
	 * report of grade throw teachers of a class 
	 * @param idclass
	 * @return report of grade throw all teachers of a class 
	 * @throws SQLException
	 */
	public MessageReport2Replay getClassesTeachers(int idclass) throws SQLException{
		ArrayList<Float> grades=new ArrayList<Float>() ;
		ArrayList<String> teachername = new ArrayList<String>();
		setQuery("SELECT user.firstname,user.lastname,avg(grade) as avg " +
				"FROM lesson,user,reg " +
				"WHERE lesson.idlesson=reg.idlesson " +
				"AND lesson.idteacher=user.iduser " +
				"AND reg.grade!='0' "+
				"AND lesson.idclass='"+idclass+"' " +
						"GROUP BY user.iduser");
		
		ResultSet rs = execQuery();
		while(rs.next())
		{
			teachername.add(rs.getString(1)+rs.getString(2));
			grades.add(rs.getFloat(3));
		}
		return new MessageReport2Replay(grades, teachername);
	}
	/**
	 * 
	 * @param idclass
	 * @return report of grade throw all courses of a class 
	 * @throws SQLException
	 */
	public MessageReport3Replay getClassesCourses(int idclass) throws SQLException{
		ArrayList<Float> grades=new ArrayList<Float>() ;
		ArrayList<String> courses = new ArrayList<String>();
		
		setQuery("SELECT course.coursename,avg(grade) as avg " +
				"FROM lesson,course,reg " +
				"WHERE lesson.idlesson=reg.idlesson " +
				"AND course.idcourse=reg.idcourse " +
				"AND reg.grade!='0' "+
				"AND lesson.idclass='"+idclass+"' " +
						"GROUP BY course.idcourse");
		
		
		ResultSet rs = execQuery();
		while(rs.next())
		{
			courses.add(rs.getString(1));
			grades.add(rs.getFloat(2));
		}
		return new MessageReport3Replay(grades, courses);
	}

}
